import constants from '@/constants';
import {
  ReportBillDto,
  ReportFlowDto,
  ReportHoldDto,
  ReportInitialAssetDto,
  ReportInitialDto,
  ReportMarketAssetDto,
  ReportMarketDailyDto,
  ReportMarketDto,
  ReportTradeDto,
  ReportUserDto,
} from '@/dtos';
import {
  BillStatusEnum,
  BillTypeEnum,
  OrderStatusEnum,
  OrderTypeEnum,
  UopPaymentTypeEnum,
} from '@/enums';
import { ok } from '@/utils';
import { Body, Controller, Post } from '@nestjs/common';
import { BaseController } from './base.controller';
import { SettingService } from '@/services/setting.service';

@Controller('report')
export class RoportController extends BaseController {
  constructor(private readonly settingService: SettingService) {
    super();
  }

  @Post('admin/user')
  async user(@Body() { params }: ReportUserDto) {
    const { current = 1, pageSize = 20, keyword, ...where } = params;

    let sql = `
      SELECT #{fields} FROM User AS u
      WHERE CONVERT_TZ(u.createdAt, 'UTC', 'Asia/Shanghai') >= '${where.startTime}'
        AND CONVERT_TZ(u.createdAt, 'UTC', 'Asia/Shanghai') <= '${where.endTime}'
      GROUP BY createDate
    `;

    const res = await this.prisma.$queryRawUnsafe<[{ count: number }]>(
      sql.replace(
        '#{fields}',
        `
          DATE_FORMAT(CONVERT_TZ(u.createdAt, 'UTC', 'Asia/Shanghai'), '%Y-%m-%d') AS createDate, 
          COUNT(DISTINCT DATE(CONVERT_TZ(u.createdAt, 'UTC', 'Asia/Shanghai'))) AS count
        `,
      ),
    );

    const list: any[] = await this.prisma.$queryRawUnsafe(
      `${sql.replace(
        '#{fields}',
        `
            DATE_FORMAT(CONVERT_TZ(u.createdAt, 'UTC', 'Asia/Shanghai'), '%Y-%m-%d') AS createDate,
            COUNT(u.id) AS registered,
            IFNULL((
                SELECT COUNT(t.id) 
                FROM User AS t
                WHERE t.isReal = true
                    AND
                      t.authAt >= createDate
                    AND 
                      t.authAt <= DATE_ADD(createDate, INTERVAL 1 DAY)
            ), 0) AS authed,
            IFNULL((
                SELECT COUNT(DISTINCT t.buyerId)
                FROM \`SubOrder\` AS t
                LEFT JOIN \`Order\` AS o ON o.id = t.orderId
                WHERE o.status = ${OrderStatusEnum.COMPLETED}
                    AND
                      o.date = createDate
            ), 0) AS buyers, 
            IFNULL((
                SELECT COUNT(DISTINCT t.sellerId)
                FROM \`SubOrder\` AS t
                LEFT JOIN \`Order\` AS o ON o.id = t.orderId
                WHERE o.status = ${OrderStatusEnum.COMPLETED}
                    AND
                      t.sellerId != ${constants.default.ownerId}
                    AND
                      o.date = createDate
            ), 0) AS sellers,
            IFNULL((
                SELECT COUNT(DISTINCT t.ownerId)
                FROM \`Bill\` AS t
                WHERE t.status = ${BillStatusEnum.COMPLETED}
                    AND (
                      t.type = ${BillTypeEnum.BUY}
                      OR
                      t.type = ${BillTypeEnum.SELL}
                    )
                    AND
                      t.ownerId != ${constants.default.ownerId}
                    AND
                      t.date = createDate
            ), 0) AS traders,
            IFNULL((
                SELECT SUM(t.total)
                FROM \`Order\` AS t
                WHERE t.status = ${OrderStatusEnum.COMPLETED}
                    AND
                      t.date = createDate
            ), 0) AS total,
            IFNULL((
                SELECT t.amt FROM FundSummary AS t WHERE t.date = createDate
            ), 0) AS summary
        `,
      )}
      ORDER BY createDate DESC
      LIMIT ${pageSize} 
      OFFSET ${(current - 1) * pageSize}
      `,
    );

    const total = res?.length ?? 0;
    const totalPage = Math.ceil(total / pageSize);
    const hasMore = current < totalPage;

    return ok({
      total,
      totalPage,
      hasMore,
      current,
      pageSize,
      list: list?.map((item) => ({
        ...item,
        registered: Number(item.registered),
        authed: Number(item.authed),
        buyers: Number(item.buyers),
        sellers: Number(item.sellers),
        traders: Number(item.traders),
        total: Number(item.total),
        summary: Number(item.summary),
      })),
    });
  }

  @Post('admin/bill')
  async bill(@Body() { params }: ReportBillDto) {
    const { current = 1, pageSize = 20, keyword, ...where } = params;

    let sql = `
      SELECT #{fields} FROM Bill AS t
      WHERE t.date >= DATE_FORMAT('${where.startTime}', '%Y-%m-%d')
        AND t.date <= DATE_FORMAT('${where.endTime}', '%Y-%m-%d')
      GROUP BY t.date
    `;

    const res = await this.prisma.$queryRawUnsafe<[{ count: number }]>(
      sql.replace(
        '#{fields}',
        `
            t.date, 
            COUNT(DISTINCT t.date) AS count            
        `,
      ),
    );

    const list: any[] = await this.prisma.$queryRawUnsafe(
      `${sql.replace(
        '#{fields}',
        `
            t.date,
            IFNULL((
                SELECT COUNT(o.id)
                FROM \`Order\` AS o
                WHERE   o.type = ${OrderTypeEnum.INITIAL}
                    AND 
                        o.status = ${OrderStatusEnum.COMPLETED}
                    AND
                        o.date = t.date
            ), 0) AS initialCount,
            IFNULL((
                SELECT SUM(o.total)
                FROM \`Order\` AS o
                WHERE   o.type = ${OrderTypeEnum.INITIAL}
                    AND 
                        o.status = ${OrderStatusEnum.COMPLETED}
                    AND
                        o.date = t.date
            ), 0) AS initialAmount,
            IFNULL((
                SELECT COUNT(o.id)
                FROM \`Order\` AS o
                WHERE   o.type = ${OrderTypeEnum.MARKET}
                    AND 
                        o.status = ${OrderStatusEnum.COMPLETED}
                    AND
                       o.date = t.date
            ), 0) AS marketCount,
            IFNULL((
                SELECT SUM(o.total)
                FROM \`Order\` AS o
                WHERE   o.type = ${OrderTypeEnum.MARKET}
                    AND 
                        o.status = ${OrderStatusEnum.COMPLETED}
                    AND
                        o.date = t.date
            ), 0) AS marketAmount,
            IFNULL((
                SELECT SUM(o.fee)
                FROM \`Order\` AS o
                WHERE   o.type = ${OrderTypeEnum.MARKET}
                    AND 
                        o.status = ${OrderStatusEnum.COMPLETED}
                    AND
                        o.date = t.date
            ), 0) AS fee,
            IFNULL((
                SELECT SUM(o.total)
                FROM \`Bill\` AS o
                WHERE   o.type = ${BillTypeEnum.WITHDRAW}
                    AND 
                        o.status != ${BillStatusEnum.CANCELED}
                    AND
                        o.date = t.date
            ), 0) AS processingAmount,
            IFNULL((
                SELECT SUM(o.total)
                FROM \`Bill\` AS o
                WHERE   o.type = ${BillTypeEnum.WITHDRAW}
                    AND (
                        o.status = ${BillStatusEnum.COMPLETED}
                        OR
                        o.status = ${BillStatusEnum.PROCESSING}
                        OR
                        (
                          SELECT COUNT(r.id) FROM RefundBill AS r WHERE r.serialNo = o.serialNo
                        ) > 0                                                  
                    )
                    AND
                        o.date = t.date
            ), 0) AS withdrawAmount,
            IFNULL((
                SELECT SUM(o.total)
                FROM \`Bill\` AS o
                WHERE   o.type = ${BillTypeEnum.WITHDRAW}
                    AND 
                        o.status = ${BillStatusEnum.COMPLETED}
                    AND
                        o.date = t.date
            ), 0) AS withdrawCompletedAmount,
            IFNULL((
                SELECT SUM(o.fee)
                FROM \`Bill\` AS o
                WHERE   o.type = ${BillTypeEnum.WITHDRAW}
                    AND 
                        o.status != ${BillStatusEnum.CANCELED}
                    AND
                        o.date = t.date
            ), 0) AS withdrawFee,
            IFNULL((
                SELECT COUNT(o.id)
                FROM \`Bill\` AS o
                WHERE   o.type = ${BillTypeEnum.WITHDRAW}
                    AND (
                        o.status = ${BillStatusEnum.COMPLETED}
                        OR
                        o.status = ${BillStatusEnum.PROCESSING}
                        OR
                        (
                          SELECT COUNT(r.id) FROM RefundBill AS r WHERE r.serialNo = o.serialNo
                        ) > 0                                                  
                    )
                    AND
                        o.date = t.date
            ), 0) AS withdrawCount,
            IFNULL((
                SELECT SUM(o.total)
                FROM \`Bill\` AS o
                WHERE   o.type = ${BillTypeEnum.RECHARGE}
                    AND 
                        o.status = ${BillStatusEnum.COMPLETED}
                    AND
                        o.date = t.date
            ), 0) AS rechargeAmount
        `,
      )}
      ORDER BY t.date DESC
      `,
    );

    const total = res?.length ?? 0;
    const totalPage = Math.ceil(total / pageSize);
    const hasMore = current < totalPage;

    return ok({
      total,
      totalPage,
      hasMore,
      current,
      pageSize,
      list: list?.map((item) => ({
        ...item,
        initialCount: Number(item.initialCount ?? 0),
        initialAmount: Number(item.initialAmount ?? 0),
        marketCount: Number(item.marketCount ?? 0),
        marketAmount: Number(item.marketAmount ?? 0),
        fee: Number(item.fee ?? 0),
        processingAmount: Number(item.processingAmount ?? 0),
        withdrawAmount: Number(item.withdrawAmount ?? 0),
        withdrawCompletedAmount: Number(item.withdrawCompletedAmount ?? 0),
        withdrawFee: Number(item.withdrawFee ?? 0),
        withdrawCount: Number(item.withdrawCount ?? 0),
        rechargeAmount: Number(item.rechargeAmount ?? 0),
      })),
    });
  }

  @Post('admin/flow')
  async flow(@Body() { params }: ReportFlowDto) {
    const setting = await this.settingService.getDefault();
    const { current = 1, pageSize = 20, keyword, ...where } = params;

    let sql = `
      SELECT #{fields} FROM Bill AS t
      WHERE t.date >= DATE_FORMAT('${where.startTime}', '%Y-%m-%d')
        AND t.date <= DATE_FORMAT('${where.endTime}', '%Y-%m-%d')
      GROUP BY t.date
    `;

    const res = await this.prisma.$queryRawUnsafe<[{ count: number }]>(
      sql.replace(
        '#{fields}',
        `
            t.date, 
            COUNT(DISTINCT t.date) AS count            
        `,
      ),
    );

    const list: any[] = await this.prisma.$queryRawUnsafe(
      `${sql.replace(
        '#{fields}',
        `
            t.date,
            IFNULL((
                SELECT COUNT(o.id)
                FROM \`Bill\` AS o
                WHERE   o.type = ${BillTypeEnum.RECHARGE} 
                    AND
                        o.payType = ${UopPaymentTypeEnum.ALIPAY}
                    AND 
                        o.status = ${BillStatusEnum.COMPLETED}
                    AND
                        o.date = t.date
            ), 0) AS alipayCount,
            IFNULL((
                SELECT COUNT(o.id)
                FROM \`Bill\` AS o
                WHERE   o.type = ${BillTypeEnum.RECHARGE} 
                    AND
                        o.payType = ${UopPaymentTypeEnum.WECHAT}
                    AND 
                        o.status = ${BillStatusEnum.COMPLETED}
                    AND
                        o.date = t.date
            ), 0) AS wechatCount,
            IFNULL((
                SELECT SUM(o.total)
                FROM \`Bill\` AS o
                WHERE   o.type = ${BillTypeEnum.RECHARGE} 
                    AND 
                        o.status = ${BillStatusEnum.COMPLETED}
                    AND
                        o.date = t.date
            ), 0) AS rechargeAmount,
            IFNULL((
                SELECT COUNT(o.id)
                FROM \`Bill\` AS o
                WHERE   o.type = ${BillTypeEnum.RECHARGE} 
                    AND 
                        o.status = ${BillStatusEnum.COMPLETED}
                    AND
                        o.date = t.date
            ), 0) AS rechargeCount,
            IFNULL((
                SELECT SUM(o.total)
                FROM \`Bill\` AS o
                WHERE   o.type = ${BillTypeEnum.WITHDRAW} 
                    AND (
                        o.status = ${BillStatusEnum.COMPLETED}
                        OR
                        o.status = ${BillStatusEnum.PROCESSING}
                        OR
                        (
                          SELECT COUNT(r.id) FROM RefundBill AS r WHERE r.serialNo = o.serialNo
                        ) > 0                                                  
                    )
                    AND
                        o.date = t.date
            ), 0) AS withdrawProcessingAmount,
            IFNULL((
                SELECT SUM(o.fee)
                FROM \`Bill\` AS o
                WHERE   o.type = ${BillTypeEnum.WITHDRAW} 
                    AND (
                        o.status = ${BillStatusEnum.COMPLETED}
                        OR
                        o.status = ${BillStatusEnum.PROCESSING}  
                        OR
                        (
                          SELECT COUNT(r.id) FROM RefundBill AS r WHERE r.serialNo = o.serialNo
                        ) > 0                                                                          
                    )
                    AND  
                        o.date = t.date
            ), 0) AS withdrawFee,
            IFNULL((
                SELECT SUM(o.amt)
                FROM \`RefundBill\` AS o
                WHERE  o.refundCoreTime = t.date
            ), 0) AS refundAmount,
            IFNULL((
                SELECT SUM(o.amt * ${setting.withdrawFee})
                FROM \`RefundBill\` AS o
                WHERE   o.refundCoreTime = t.date
            ), 0) AS refundFee,
            IFNULL((
                SELECT SUM(o.total)
                FROM \`Bill\` AS o
                WHERE   o.type = ${BillTypeEnum.WITHDRAW} 
                    AND 
                        o.status = ${BillStatusEnum.COMPLETED}
                    AND
                        o.date = t.date
            ), 0) AS withdrawAmount,
            IFNULL((
                SELECT COUNT(o.id)
                FROM \`Bill\` AS o
                WHERE   o.type = ${BillTypeEnum.WITHDRAW} 
                    AND 
                        o.status = ${BillStatusEnum.COMPLETED}
                    AND
                        o.date = t.date
            ), 0) AS withdrawCount,
            IFNULL((
                SELECT SUM(o.total)
                FROM \`Bill\` AS o
                WHERE   o.type = ${BillTypeEnum.WITHDRAW} 
                    AND 
                        o.status = ${BillStatusEnum.REJECTED}
                    AND
                        o.serialNo IS NOT NULL
                    AND
                        o.date = t.date
            ), 0) AS withdrawRejectedAmount,
            IFNULL((
                SELECT COUNT(o.id)
                FROM \`Bill\` AS o
                WHERE   o.type = ${BillTypeEnum.WITHDRAW} 
                    AND 
                        o.status = ${BillStatusEnum.REJECTED}
                    AND
                        o.serialNo IS NOT NULL
                    AND
                        o.date = t.date
            ), 0) AS withdrawRejectedCount
        `,
      )}
      ORDER BY t.date DESC
      `,
    );

    const total = res?.length ?? 0;
    const totalPage = Math.ceil(total / pageSize);
    const hasMore = current < totalPage;

    return ok({
      total,
      totalPage,
      hasMore,
      current,
      pageSize,
      list: list?.map((item) => ({
        ...item,
        alipayCount: Number(item.alipayCount ?? 0),
        wechatCount: Number(item.wechatCount ?? 0),
        channel: `支付宝: ${Number(item.alipayCount ?? 0)}笔，微信: ${Number(item.wechatCount ?? 0)}笔`,
        rechargeAmount: Number(item.rechargeAmount ?? 0),
        rechargeCount: Number(item.rechargeCount ?? 0),
        withdrawProcessingAmount: Number(item.withdrawProcessingAmount ?? 0),
        withdrawFee: Number(item.withdrawFee ?? 0),
        refundAmount: Number(item.refundAmount ?? 0),
        refundFee: Number(item.refundFee ?? 0),
        withdrawAmount: Number(item.withdrawAmount ?? 0),
        withdrawCount: Number(item.withdrawCount ?? 0),
        withdrawRejectedAmount: Number(item.withdrawRejectedAmount ?? 0),
        withdrawRejectedCount: Number(item.withdrawRejectedCount ?? 0),
      })),
    });
  }

  @Post('admin/trade')
  async trade(@Body() { params, sort }: ReportTradeDto) {
    const { current = 1, pageSize = 20, keyword, ...where } = params;

    let sql = `
      SELECT #{fields} FROM \`User\` AS t
      WHERE t.isDeleted = false
        AND t.isReal = true
        ${!!where.realname ? `AND t.realname LIKE '%${where.realname}%'` : ''}
        ${
          !!where.initialAmount
            ? `AND IFNULL((
                SELECT
                    SUM(o.total)
                FROM \`Order\` AS o
                WHERE   o.type = ${OrderTypeEnum.INITIAL} 
                    AND 
                        o.status = ${OrderStatusEnum.COMPLETED}
                    AND
                        o.ownerId = t.id
                    AND CONVERT_TZ(o.createdAt, 'UTC', 'Asia/Shanghai') >= '${where.startTime}'
                    AND CONVERT_TZ(o.createdAt, 'UTC', 'Asia/Shanghai') <= '${where.endTime}'
            ), 0) >= ${where.initialAmount}`
            : ''
        }
        ${
          !!where.marketAmount
            ? `AND IFNULL((
                SELECT
                    SUM(o.total)
                FROM \`Order\` AS o
                WHERE   o.type = ${OrderTypeEnum.MARKET} 
                    AND 
                        o.status = ${OrderStatusEnum.COMPLETED}
                    AND
                        o.ownerId = t.id
                    AND CONVERT_TZ(o.createdAt, 'UTC', 'Asia/Shanghai') >= '${where.startTime}'
                    AND CONVERT_TZ(o.createdAt, 'UTC', 'Asia/Shanghai') <= '${where.endTime}'
            ), 0) >= ${where.marketAmount}`
            : ''
        }
        ${
          !!where.sellAmount
            ? `AND IFNULL((
                SELECT
                    SUM(o.total)
                FROM \`Bill\` AS o
                WHERE   o.type = ${BillTypeEnum.SELL} 
                    AND 
                        o.status = ${BillStatusEnum.COMPLETED}          
                     AND
                        o.ownerId = t.id
                    AND CONVERT_TZ(o.createdAt, 'UTC', 'Asia/Shanghai') >= '${where.startTime}'
                    AND CONVERT_TZ(o.createdAt, 'UTC', 'Asia/Shanghai') <= '${where.endTime}'
            ), 0) >= ${where.sellAmount}`
            : ''
        }
        ${
          !!where.rechargeAmount
            ? `AND IFNULL((
                SELECT
                    SUM(o.total)
                FROM \`Bill\` AS o
                WHERE   o.type = ${BillTypeEnum.RECHARGE} 
                    AND 
                        o.status = ${BillStatusEnum.COMPLETED}          
                     AND
                        o.ownerId = t.id
                    AND CONVERT_TZ(o.createdAt, 'UTC', 'Asia/Shanghai') >= '${where.startTime}'
                    AND CONVERT_TZ(o.createdAt, 'UTC', 'Asia/Shanghai') <= '${where.endTime}'
            ), 0) >= ${where.rechargeAmount}`
            : ''
        }
        ${
          !!where.withdrawAmount
            ? `AND IFNULL((
                SELECT
                    SUM(o.total)
                FROM \`Bill\` AS o
                WHERE   o.type = ${BillTypeEnum.WITHDRAW} 
                    AND 
                        o.status = ${BillStatusEnum.COMPLETED}          
                     AND
                        o.ownerId = t.id
                    AND CONVERT_TZ(o.createdAt, 'UTC', 'Asia/Shanghai') >= '${where.startTime}'
                    AND CONVERT_TZ(o.createdAt, 'UTC', 'Asia/Shanghai') <= '${where.endTime}'
            ), 0) >= ${where.withdrawAmount}`
            : ''
        }
    `;

    const [{ count }] = await this.prisma.$queryRawUnsafe<[{ count: number }]>(
      sql.replace(
        '#{fields}',
        `
            COUNT(t.id) AS count            
        `,
      ),
    );

    const list: any[] = await this.prisma.$queryRawUnsafe(
      `${sql.replace(
        '#{fields}',
        `
            t.id, t.realname, t.createdAt,
            IFNULL((
                SELECT
                    SUM(o.total)
                FROM \`Order\` AS o
                WHERE   o.type = ${OrderTypeEnum.INITIAL} 
                    AND 
                        o.status = ${OrderStatusEnum.COMPLETED}
                    AND
                        o.ownerId = t.id
                    AND CONVERT_TZ(o.createdAt, 'UTC', 'Asia/Shanghai') >= '${where.startTime}'
                    AND CONVERT_TZ(o.createdAt, 'UTC', 'Asia/Shanghai') <= '${where.endTime}'
            ), 0) AS initialAmount,
            IFNULL((
                SELECT
                    SUM(o.total)
                FROM \`Order\` AS o
                WHERE   o.type = ${OrderTypeEnum.MARKET} 
                    AND 
                        o.status = ${OrderStatusEnum.COMPLETED}
                    AND
                        o.ownerId = t.id
                    AND CONVERT_TZ(o.createdAt, 'UTC', 'Asia/Shanghai') >= '${where.startTime}'
                    AND CONVERT_TZ(o.createdAt, 'UTC', 'Asia/Shanghai') <= '${where.endTime}'
            ), 0) AS marketAmount,
            IFNULL((
                SELECT
                    SUM(o.total)
                FROM \`Bill\` AS o
                WHERE   o.type = ${BillTypeEnum.SELL} 
                    AND 
                        o.status = ${BillStatusEnum.COMPLETED}          
                     AND
                        o.ownerId = t.id
                    AND CONVERT_TZ(o.createdAt, 'UTC', 'Asia/Shanghai') >= '${where.startTime}'
                    AND CONVERT_TZ(o.createdAt, 'UTC', 'Asia/Shanghai') <= '${where.endTime}'
            ), 0) AS sellAmount,
            IFNULL((
                SELECT
                    SUM(o.total)
                FROM \`Bill\` AS o
                WHERE   o.type = ${BillTypeEnum.RECHARGE} 
                    AND 
                        o.status = ${BillStatusEnum.COMPLETED}          
                     AND
                        o.ownerId = t.id
                    AND CONVERT_TZ(o.createdAt, 'UTC', 'Asia/Shanghai') >= '${where.startTime}'
                    AND CONVERT_TZ(o.createdAt, 'UTC', 'Asia/Shanghai') <= '${where.endTime}'
            ), 0) AS rechargeAmount,
            IFNULL((
                SELECT
                    SUM(o.total)
                FROM \`Bill\` AS o
                WHERE   o.type = ${BillTypeEnum.WITHDRAW} 
                    AND 
                        o.status = ${BillStatusEnum.COMPLETED}         
                    AND
                        o.ownerId = t.id
                    AND CONVERT_TZ(o.createdAt, 'UTC', 'Asia/Shanghai') >= '${where.startTime}'
                    AND CONVERT_TZ(o.createdAt, 'UTC', 'Asia/Shanghai') <= '${where.endTime}'
            ), 0) AS withdrawAmount
        `,
      )}
      ORDER BY ${
        !!sort && Object.keys(sort).length > 0
          ? Object.entries(sort)
              .map(([k, v]) => {
                if (
                  [
                    'initialAmount',
                    'marketAmount',
                    'sellAmount',
                    'rechargeAmount',
                    'withdrawAmount',
                  ].includes(k)
                ) {
                  return `${k} ${v === 'descend' ? 'DESC' : 'ASC'}`;
                }
                return `t.${k} ${v === 'descend' ? 'DESC' : 'ASC'}`;
              })
              .join(',')
          : 'initialAmount DESC'
      }
      LIMIT ${pageSize} 
      OFFSET ${(current - 1) * pageSize}
      `,
    );

    const total = count ? Number(count) : 0;
    const totalPage = Math.ceil(total / pageSize);
    const hasMore = current < totalPage;

    return ok({
      total,
      totalPage,
      hasMore,
      current,
      pageSize,
      list: list?.map((item) => ({
        ...item,
        initialAmount: Number(item.initialAmount),
        marketAmount: Number(item.marketAmount),
        sellAmount: Number(item.sellAmount),
        rechargeAmount: Number(item.rechargeAmount),
        withdrawAmount: Number(item.withdrawAmount),
      })),
    });
  }

  @Post('admin/hold')
  async hold(@Body() { params, sort }: ReportHoldDto) {
    const { current = 1, pageSize = 20, keyword, ...where } = params;

    let sql = `
      SELECT #{fields} FROM Token AS t
      LEFT JOIN \`User\` AS u ON u.id = t.ownerId
      LEFT JOIN \`Asset\` AS a ON a.id = t.assetId
      WHERE t.isDeleted = false        
        AND t.ownerId != ${constants.default.ownerId}
        ${!!where.assetId ? `AND t.assetId = ${where.assetId}` : ''}
        ${!!where.userId ? `AND t.ownerId = ${where.userId}` : ''}
        ${!!where.realname ? `AND u.realname LIKE '%${where.realname}%'` : ''}
        ${!!where.title ? `AND a.title LIKE '%${where.title}%'` : ''}
      GROUP BY t.assetId, u.id
      HAVING hold > 0
      ${!!where.min ? `AND hold >= ${where.min}` : ''}
      ${!!where.max ? `AND hold <= ${where.max}` : ''}    
    `;

    const res = await this.prisma.$queryRawUnsafe<[{ count: number }]>(
      `${sql.replace(
        '#{fields}',
        `   
            COUNT(t.id) AS hold,
            COUNT(DISTINCT t.assetId) AS count
        `,
      )}
      `,
    );

    const list: any[] = await this.prisma.$queryRawUnsafe(
      `${sql.replace(
        '#{fields}',
        `
            COUNT(t.id) AS hold,
            u.id, u.realname, t.assetId, a.title, a.image    
        `,
      )}
      ORDER BY ${
        !!sort && Object.keys(sort).length > 0
          ? Object.entries(sort)
              .map(([k, v]) => {
                if (['hold'].includes(k)) {
                  return `${k} ${v === 'descend' ? 'DESC' : 'ASC'}`;
                }
                return `t.${k} ${v === 'descend' ? 'DESC' : 'ASC'}`;
              })
              .join(',')
          : 'hold DESC'
      }
      LIMIT ${pageSize} 
      OFFSET ${(current - 1) * pageSize}
      `,
    );

    const total = res?.length ?? 0;
    const totalPage = Math.ceil(total / pageSize);
    const hasMore = current < totalPage;

    return ok({
      total,
      totalPage,
      hasMore,
      current,
      pageSize,
      list: list?.map((item) => ({
        ...item,
        hold: Number(item.hold),
      })),
    });
  }

  @Post('admin/initial')
  async initial(@Body() { params, sort }: ReportInitialDto) {
    const { current = 1, pageSize = 20, keyword, ...where } = params;

    let sql = `
      SELECT #{fields} FROM \`Order\` AS t
      LEFT JOIN \`Asset\` AS a ON a.id = t.assetId
      LEFT JOIN \`Plan\` AS p ON p.id = t.planId
      LEFT JOIN \`User\` AS u ON u.id = t.ownerId
      WHERE t.isDeleted = false
        AND t.type = ${OrderTypeEnum.INITIAL}
        AND t.status = ${OrderStatusEnum.COMPLETED}
        ${!!where.startTime ? `AND CONVERT_TZ(t.createdAt, 'UTC', 'Asia/Shanghai') >= '${where.startTime}'` : ''}
        ${!!where.endTime ? `AND CONVERT_TZ(t.createdAt, 'UTC', 'Asia/Shanghai') <= '${where.endTime}'` : ''}
        ${!!where.no ? `AND t.no LIKE '%${where.no}%'` : ''}
        ${!!where.ownerId ? `AND u.id = ${where.ownerId}` : ''}
        ${!!where.realname ? `AND u.realname LIKE '%${where.realname}%'` : ''}
        ${!!where.assetId ? `AND a.id = ${where.assetId}` : ''}
        ${!!where.title ? `AND a.title LIKE '%${where.title}%'` : ''}
    `;

    const [{ count }] = await this.prisma.$queryRawUnsafe<[{ count: number }]>(
      sql.replace(
        '#{fields}',
        `
            COUNT(t.id) AS count
        `,
      ),
    );

    const list: any[] = await this.prisma.$queryRawUnsafe(
      `${sql.replace(
        '#{fields}',
        `
            t.id, t.no, t.total, t.assetId, p.price, a.title, t.ownerId, u.realname, t.createdAt
        `,
      )}
      ORDER BY ${
        !!sort && Object.keys(sort).length > 0
          ? Object.entries(sort)
              .map(([k, v]) => {
                return `t.${k} ${v === 'descend' ? 'DESC' : 'ASC'}`;
              })
              .join(',')
          : 't.createdAt DESC'
      }
      LIMIT ${pageSize}
      OFFSET ${(current - 1) * pageSize}
      `,
    );

    const total = count ? Number(count) : 0;
    const totalPage = Math.ceil(total / pageSize);
    const hasMore = current < totalPage;

    return ok({
      total,
      totalPage,
      hasMore,
      current,
      pageSize,
      list: list?.map((item) => ({
        ...item,
        total: Number(item.total),
        price: Number(item.price),
      })),
    });
  }

  @Post('admin/market')
  async market(@Body() { params, sort }: ReportMarketDto) {
    const { current = 1, pageSize = 20, keyword, ...where } = params;

    let sql = `
      SELECT #{fields} FROM \`SubOrder\` AS t
      LEFT JOIN \`Order\` AS o ON o.id = t.orderId
      LEFT JOIN \`Asset\` AS a ON a.id = o.assetId
      LEFT JOIN \`User\` AS b ON b.id = t.buyerId
      LEFT JOIN \`User\` AS s ON s.id = t.sellerId
      WHERE o.isDeleted = false
        AND o.type = ${OrderTypeEnum.MARKET}
        AND o.status = ${OrderStatusEnum.COMPLETED}
        ${!!where.startTime ? `AND CONVERT_TZ(o.createdAt, 'UTC', 'Asia/Shanghai') >= '${where.startTime}'` : ''}
        ${!!where.endTime ? `AND CONVERT_TZ(o.createdAt, 'UTC', 'Asia/Shanghai') <= '${where.endTime}'` : ''}
        ${!!where.no ? `AND t.no LIKE '%${where.no}%'` : ''}
        ${!!where.assetId ? `AND a.id = ${where.assetId}` : ''}
        ${!!where.title ? `AND a.title LIKE '%${where.title}%'` : ''}
        ${!!where.buyerId ? `AND b.id = ${where.buyerId}` : ''}
        ${!!where.buyerName ? `AND b.realname LIKE '%${where.buyerName}%'` : ''}
        ${!!where.sellerId ? `AND s.id = ${where.sellerId}` : ''}
        ${!!where.sellerName ? `AND s.realname LIKE '%${where.sellerName}%'` : ''}
    `;

    const [{ count }] = await this.prisma.$queryRawUnsafe<[{ count: number }]>(
      sql.replace(
        '#{fields}',
        `
            COUNT(t.id) AS count
        `,
      ),
    );

    const list: any[] = await this.prisma.$queryRawUnsafe(
      `${sql.replace(
        '#{fields}',
        `
            t.id, t.no, t.price, t.fee,
            t.assetId, a.title, 
            t.buyerId, b.realname AS buyerName,
            t.sellerId, s.realname AS sellerName, 
            t.createdAt
        `,
      )}
      ORDER BY ${
        !!sort && Object.keys(sort).length > 0
          ? Object.entries(sort)
              .map(([k, v]) => {
                return `t.${k} ${v === 'descend' ? 'DESC' : 'ASC'}`;
              })
              .join(',')
          : 't.createdAt DESC'
      }
      LIMIT ${pageSize}
      OFFSET ${(current - 1) * pageSize}
      `,
    );

    const total = count ? Number(count) : 0;
    const totalPage = Math.ceil(total / pageSize);
    const hasMore = current < totalPage;

    return ok({
      total,
      totalPage,
      hasMore,
      current,
      pageSize,
      list: list?.map((item) => ({
        ...item,
        price: Number(item.price),
        fee: Number(item.fee),
      })),
    });
  }

  @Post('admin/initial/asset')
  async initialAsset(@Body() { params, sort }: ReportInitialAssetDto) {
    const { current = 1, pageSize = 20, keyword, ...where } = params;

    let sql = `
      SELECT #{fields} FROM \`Order\` AS t
      LEFT JOIN \`Asset\` AS a ON a.id = t.assetId
      WHERE t.isDeleted = false
        AND t.type = ${OrderTypeEnum.INITIAL}
        AND t.status = ${OrderStatusEnum.COMPLETED}
        ${!!where.assetId ? `AND t.assetId = ${where.assetId}` : ''}
        ${!!where.title ? `AND a.title LIKE '%${where.title}%'` : ''}
        ${!!where.startTime ? `AND CONVERT_TZ(t.createdAt, 'UTC', 'Asia/Shanghai') >= '${where.startTime}'` : ''}
        ${!!where.endTime ? `AND CONVERT_TZ(t.createdAt, 'UTC', 'Asia/Shanghai') <= '${where.endTime}'` : ''}
      GROUP BY t.assetId
    `;

    const res = await this.prisma.$queryRawUnsafe<[{ count: number }]>(
      sql.replace(
        '#{fields}',
        `
            COUNT(t.id) AS count
        `,
      ),
    );

    const list: any[] = await this.prisma.$queryRawUnsafe(
      `${sql.replace(
        '#{fields}',
        `
            t.assetId, a.limit, a.title, a.image,
            SUM(t.num) AS count,
            SUM(t.total) AS amount,
            SUM(t.fee) AS fee
        `,
      )}
      ORDER BY ${
        !!sort && Object.keys(sort).length > 0
          ? Object.entries(sort)
              .map(([k, v]) => {
                if (['count', 'amount', 'fee'].includes(k)) {
                  return `${k} ${v === 'descend' ? 'DESC' : 'ASC'}`;
                }
                return `t.${k} ${v === 'descend' ? 'DESC' : 'ASC'}`;
              })
              .join(',')
          : 't.assetId DESC'
      }
      LIMIT ${pageSize}
      OFFSET ${(current - 1) * pageSize}
      `,
    );

    const total = res ? res.length : 0;
    const totalPage = Math.ceil(total / pageSize);
    const hasMore = current < totalPage;

    return ok({
      total,
      totalPage,
      hasMore,
      current,
      pageSize,
      list: list?.map((item) => ({
        ...item,
        count: Number(item.count),
        amount: Number(item.amount),
        fee: Number(item.fee),
      })),
    });
  }

  @Post('admin/market/asset')
  async marketAsset(@Body() { params, sort }: ReportMarketAssetDto) {
    const { current = 1, pageSize = 20, keyword, ...where } = params;

    let sql = `
      SELECT #{fields} FROM \`Order\` AS t
      LEFT JOIN \`Asset\` AS a ON a.id = t.assetId
      WHERE t.isDeleted = false
        AND t.type = ${OrderTypeEnum.MARKET}
        AND t.status = ${OrderStatusEnum.COMPLETED}
        ${!!where.assetId ? `AND t.assetId = ${where.assetId}` : ''}
        ${!!where.title ? `AND a.title LIKE '%${where.title}%'` : ''}
        ${!!where.startTime ? `AND CONVERT_TZ(t.createdAt, 'UTC', 'Asia/Shanghai') >= '${where.startTime}'` : ''}
        ${!!where.endTime ? `AND CONVERT_TZ(t.createdAt, 'UTC', 'Asia/Shanghai') <= '${where.endTime}'` : ''}
      GROUP BY t.assetId
    `;

    const res = await this.prisma.$queryRawUnsafe<[{ count: number }]>(
      sql.replace(
        '#{fields}',
        `
            COUNT(t.id) AS count
        `,
      ),
    );

    const list: any[] = await this.prisma.$queryRawUnsafe(
      `${sql.replace(
        '#{fields}',
        `
            t.assetId, a.title, a.image,
            COUNT(t.id) AS count,
            SUM(t.total) AS amount,
            SUM(t.fee) AS fee
        `,
      )}
      ORDER BY ${
        !!sort && Object.keys(sort).length > 0
          ? Object.entries(sort)
              .map(([k, v]) => {
                if (['count', 'amount', 'fee'].includes(k)) {
                  return `${k} ${v === 'descend' ? 'DESC' : 'ASC'}`;
                }
                return `t.${k} ${v === 'descend' ? 'DESC' : 'ASC'}`;
              })
              .join(',')
          : 't.assetId DESC'
      }
      LIMIT ${pageSize}
      OFFSET ${(current - 1) * pageSize}
      `,
    );

    const total = res ? res.length : 0;
    const totalPage = Math.ceil(total / pageSize);
    const hasMore = current < totalPage;

    return ok({
      total,
      totalPage,
      hasMore,
      current,
      pageSize,
      list: list?.map((item) => ({
        ...item,
        count: Number(item.count),
        amount: Number(item.amount),
        fee: Number(item.fee),
      })),
    });
  }

  @Post('admin/market/daily')
  async marketDaily(@Body() { params, sort }: ReportMarketDailyDto) {
    const { current = 1, pageSize = 20, keyword, ...where } = params;

    let sql = `
      SELECT #{fields} FROM \`Order\` AS t
      WHERE t.isDeleted = false
        AND t.type = ${OrderTypeEnum.MARKET}
        AND t.status = ${OrderStatusEnum.COMPLETED}
        ${!!where.startTime ? `AND CONVERT_TZ(t.createdAt, 'UTC', 'Asia/Shanghai') >= '${where.startTime}'` : ''}
        ${!!where.endTime ? `AND CONVERT_TZ(t.createdAt, 'UTC', 'Asia/Shanghai') <= '${where.endTime}'` : ''}
      GROUP BY t.date
    `;

    const res = await this.prisma.$queryRawUnsafe<[{ count: number }]>(
      sql.replace(
        '#{fields}',
        `
            t.date,
            COUNT(t.id) AS count
        `,
      ),
    );

    const list: any[] = await this.prisma.$queryRawUnsafe(
      `${sql.replace(
        '#{fields}',
        `
            t.date,
            COUNT(t.id) AS count,
            SUM(t.total) AS amount,
            SUM(t.fee) AS fee
        `,
      )}
      ORDER BY ${
        !!sort && Object.keys(sort).length > 0
          ? Object.entries(sort)
              .map(([k, v]) => {
                if (['count', 'amount', 'fee'].includes(k)) {
                  return `${k} ${v === 'descend' ? 'DESC' : 'ASC'}`;
                }
                return `t.${k} ${v === 'descend' ? 'DESC' : 'ASC'}`;
              })
              .join(',')
          : 't.date DESC'
      }
      LIMIT ${pageSize} 
      OFFSET ${(current - 1) * pageSize}
      `,
    );

    const total = res ? res.length : 0;
    const totalPage = Math.ceil(total / pageSize);
    const hasMore = current < totalPage;

    return ok({
      total,
      totalPage,
      hasMore,
      current,
      pageSize,
      list: list?.map((item) => ({
        ...item,
        count: Number(item.count),
        amount: Number(item.amount),
        fee: Number(item.fee),
      })),
    });
  }
}
